const char* COURSE_NAMES[5] = {"高等数学", "线性代数", "高级语言程序设计", "大学物理", "离散数学"};

// 数据库操作函数声明
int init_database();
int add_student_db(const char* name, int id, const char* classroom);
int update_student_db(int id, const char* name, const char* classroom);
int delete_student_db(int id);
int input_scores_db(int id, float* daily_scores, float* final_scores);
int query_student_by_id_db(int id, char* name, char* classroom, 
                          float* daily_scores, float* final_scores, float* total_score, int* rank);
int query_student_by_name_db(const char* name, char* classroom, int* id, 
                            float* daily_scores, float* final_scores, float* total_score, int* rank);
int statistics_class_db(const char* classroom, int* count, float* total, float* max, float* min);
float calculate_pass_score(float known_score, int course_idx, int score_type);
void close_database();

sqlite3* db = NULL;

// 辅助函数：转义SQL字符串中的单引号
char* escape_sql_string(const char* str) {
    if (!str) return NULL;
    
    int len = strlen(str);
    int escaped_len = len;
    for (int i = 0; i < len; i++) {
        if (str[i] == '\'') escaped_len++;
    }
    
    char* escaped = (char*)malloc((escaped_len + 1) * sizeof(char));
    if (!escaped) return NULL;
    
    int j = 0;
    for (int i = 0; i < len; i++) {
        if (str[i] == '\'') {
            escaped[j++] = '\'';
        }
        escaped[j++] = str[i];
    }
    escaped[j] = '\0';
    
    return escaped;
}

// 初始化数据库并创建表
int init_database() {
    int rc = sqlite3_open(DB_FILENAME, &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db));
        return 0;
    }
    
    // 创建学生表
    const char* sql_students = "CREATE TABLE IF NOT EXISTS students ("
                                "id INTEGER PRIMARY KEY,"
                                "name TEXT NOT NULL,"
                                "classroom TEXT"
                                ")";
                            
    // 创建成绩表（包含平时分和期末分）
    const char* sql_scores = "CREATE TABLE IF NOT EXISTS scores ("
                              "id INTEGER PRIMARY KEY,"
                              "student_id INTEGER NOT NULL,"
                              "course1_daily REAL,"
                              "course1_final REAL,"
                              "course2_daily REAL,"
                              "course2_final REAL,"
                              "course3_daily REAL,"
                              "course3_final REAL,"
                              "course4_daily REAL,"
                              "course4_final REAL,"
                              "course5_daily REAL,"
                              "course5_final REAL,"
                              "total_score REAL,"
                              "FOREIGN KEY (student_id) REFERENCES students (id)"
                              ")";
                            
    char* errmsg = NULL;
    rc = sqlite3_exec(db, sql_students, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建学生表失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        sqlite3_close(db);
        return 0;
    }
    
    rc = sqlite3_exec(db, sql_scores, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建成绩表失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        sqlite3_close(db);
        return 0;
    }
    
    printf("数据库初始化成功\n");
    return 1;
}

// 关闭数据库连接
void close_database() {
    if (db) {
        sqlite3_close(db);
        db = NULL;
    }
}

// 学生信息管理模块
void add_student() {
    if (db == NULL && !init_database()) {
        printf("数据库初始化失败，无法添加学生\n");
        return;
    }

    char name[MAX_NAME_LENGTH];
    int id;
    char classroom[MAX_NAME_LENGTH];
    
    printf("请输入学生姓名：");
    scanf("%s", name);
    printf("请输入学号：");
    scanf("%d", &id);
    printf("请输入班级：");
    scanf("%s", classroom);
    
    if (add_student_db(name, id, classroom)) {
        printf("学生信息添加成功！\n");
    } else {
        printf("学生信息添加失败！\n");
    }
}

int add_student_db(const char* name, int id, const char* classroom) {
    char sql[512];
    char* errmsg = NULL;
    char* escaped_name = escape_sql_string(name);
    char* escaped_classroom = escape_sql_string(classroom);
    
    if (!escaped_name || !escaped_classroom) {
        printf("内存分配失败！\n");
        if (escaped_name) free(escaped_name);
        if (escaped_classroom) free(escaped_classroom);
        return 0;
    }
    
    // 检查学号是否已存在
    sprintf(sql, "SELECT id FROM students WHERE id = %d", id);
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc == SQLITE_OK && sqlite3_step(stmt) == SQLITE_ROW) {
        sqlite3_finalize(stmt);
        printf("学号已存在！\n");
        free(escaped_name);
        free(escaped_classroom);
        return 0;
    }
    sqlite3_finalize(stmt);
    
    // 插入新学生
    sprintf(sql, "INSERT INTO students (id, name, classroom) VALUES (%d, '%s', '%s')", 
            id, escaped_name, escaped_classroom);
    rc = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "插入学生数据失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        free(escaped_name);
        free(escaped_classroom);
        return 0;
    }
    
    // 插入初始成绩记录（所有成绩设为0）
    sprintf(sql, "INSERT INTO scores (student_id, course1_daily, course1_final, "
                 "course2_daily, course2_final, course3_daily, course3_final, "
                 "course4_daily, course4_final, course5_daily, course5_final, total_score) "
                 "VALUES (%d, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)", id);
    rc = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "插入成绩记录失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        free(escaped_name);
        free(escaped_classroom);
        return 0;
    }
    
    free(escaped_name);
    free(escaped_classroom);
    return 1;
}

void update_student() {
    if (db == NULL && !init_database()) {
        printf("数据库初始化失败，无法更新学生\n");
        return;
    }

    int id;
    printf("请输入要修改的学生学号：");
    scanf("%d", &id);

    char name[MAX_NAME_LENGTH];
    char classroom[MAX_NAME_LENGTH];
    
    // 先查询当前学生信息
    sprintf(name, "未知");
    sprintf(classroom, "未知");
    float daily_scores[5] = {0};
    float final_scores[5] = {0};
    float total = 0;
    int rank = 0;
    if (!query_student_by_id_db(id, name, classroom, daily_scores, final_scores, &total, &rank)) {
        printf("未找到该学生！\n");
        return;
    }
    
    printf("当前学生姓名：%s，班级：%s\n", name, classroom);
    printf("请输入新的学生姓名：");
    scanf("%s", name);
    printf("请输入新的班级：");
    scanf("%s", classroom);
    
    if (update_student_db(id, name, classroom)) {
        printf("学生信息修改成功！\n");
    } else {
        printf("学生信息修改失败！\n");
    }
}

int update_student_db(int id, const char* name, const char* classroom) {
    char sql[512];
    char* errmsg = NULL;
    char* escaped_name = escape_sql_string(name);
    char* escaped_classroom = escape_sql_string(classroom);
    
    if (!escaped_name || !escaped_classroom) {
        printf("内存分配失败！\n");
        if (escaped_name) free(escaped_name);
        if (escaped_classroom) free(escaped_classroom);
        return 0;
    }
    
    sprintf(sql, "UPDATE students SET name = '%s', classroom = '%s' WHERE id = %d", 
            escaped_name, escaped_classroom, id);
    int rc = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "更新学生数据失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        free(escaped_name);
        free(escaped_classroom);
        return 0;
    }
    
    free(escaped_name);
    free(escaped_classroom);
    return 1;
}

void delete_student() {
    if (db == NULL && !init_database()) {
        printf("数据库初始化失败，无法删除学生\n");
        return;
    }

    int id;
    printf("请输入要删除的学生学号：");
    scanf("%d", &id);

    char name[MAX_NAME_LENGTH];
    char classroom[MAX_NAME_LENGTH];
    float daily_scores[5] = {0};
    float final_scores[5] = {0};
    float total = 0;
    int rank = 0;
    if (!query_student_by_id_db(id, name, classroom, daily_scores, final_scores, &total, &rank)) {
        printf("未找到该学生！\n");
        return;
    }
    
    printf("即将删除学生：%s (学号：%d，班级：%s)\n", name, id, classroom);
    printf("确认删除？(1-是/0-否)：");
    int confirm;
    scanf("%d", &confirm);
    if (confirm != 1) {
        printf("已取消删除操作\n");
        return;
    }
    
    if (delete_student_db(id)) {
        printf("学生信息删除成功！\n");
    } else {
        printf("学生信息删除失败！\n");
    }
}

int delete_student_db(int id) {
    char sql[512];
    char* errmsg = NULL;
    
    // 先删除成绩记录
    sprintf(sql, "DELETE FROM scores WHERE student_id = %d", id);
    int rc = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "删除成绩记录失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        return 0;
    }
    
    // 再删除学生记录
    sprintf(sql, "DELETE FROM students WHERE id = %d", id);
    rc = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "删除学生数据失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        return 0;
    }
    
    return 1;
}
int main() {
    int main_choice, student_choice, score_choice;

    while (1) {
        display_menu();
        scanf("%d", &main_choice);

        switch (main_choice) {
            case 1:
                while (1) {
                    display_student_menu();
                    scanf("%d", &student_choice);
                    if (student_choice == 4) break;

                    switch (student_choice) {
                        case 1: add_student(); break;
                        case 2: update_student(); break;
                        case 3: delete_student(); break;
                        default: printf("无效的选择！\n");
                    }
                }
                break;

            case 2:
                while (1) {
                    display_score_menu();
                    scanf("%d", &score_choice);
                    if (score_choice == 4) break;

                    switch (score_choice) {
                        case 1: input_scores(); break;
                        case 2: query_scores(); break;
                        case 3: statistics_scores(); break;
                        default: printf("无效的选择！\n");
                    }
                }
                break;

            case 3:
                calculate_pass_score_interface();
                break;

            case 4:
                printf("感谢使用学生信息管理系统，再见！\n");
                close_database();
                return 0;

            default:
                printf("无效的选择！\n");
        }
    }
}    